package DAO;

// <editor-fold defaultstate="collapsed" desc="HeaderFiles">
import DTO.Customer;
import DTO.Address;
import Exception.EmptySet;
import Exception.KwickKioskDBException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;// </editor-fold>
import java.util.*;

/**
 * This class represents abstraction for data access from customer table in database.
 * This class is called from DAO as a proxy.
 * @see DAO
 * @Date Mar 30, 2011
 * @author Tony Lei
 */
public class CustomerDAO {

    private Connection conn;
    private static final Logger logger = Logger.getLogger(CustomerDAO.class.getName());

    /**
     * constructor for this class.
     * @param conn connection object
     */
    public CustomerDAO(Connection conn) {
        this.conn = conn;
    }

    /**
     * This method inserts customer information into database
     * @param passCode  a string represents encrypted customer password
     * @param firstName a string represents customer first name
     * @param lastName a string represents customer last name
     * @param email a string represents customer email
     * @param phoneNumber a string represents customer phone number
     * @return an integer customer id
     * @throws {@link KwickKioskDBException}
     */
    public int registerCustomer(String passCode, String firstName,
            String lastName, String email, String phoneNumber) throws KwickKioskDBException
    {

        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "INSERT INTO customer "
                    + "(passcode, firstName, lastName, email, phone) VALUES ("
                    + "'" + passCode + "', "
                    + "'" + firstName + "', "
                    + "'" + lastName + "', "
                    + "'" + email + "', "
                    + "'" + phoneNumber + "')";
            if (updateDB(sqlText)) {
                sqlText = "select max(c.csid) from customer c";
                ResultSet rs = sqlStatement.executeQuery(sqlText);
                if (rs.next()) {
                    int newCustomerID = rs.getInt(1);
                    rs.close();
                    sqlStatement.close();
                    return newCustomerID;
                } else {
                    rs.close();
                    sqlStatement.close();
                    return -1;
                }
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "CustomerDAO : registerCustomer" + ex);
        }
        return -1;
    }

     /**
     * This method updates customer information in database.
     * @param c customer data transfer object
     * @return a boolean flag indicates success/failure of transaction
     * @throws {@link KwickKioskDBException}
     */
    protected boolean updateCustomer(Customer c) throws KwickKioskDBException {
        String sqlText = "UPDATE customer SET passcode='"
                + c.getPassCode() + "', "
                + "firstName='" + c.getFirstName() + "', "
                + "lastName='" + c.getLastName() + "', "
                + "email='" + c.getEmail() + "', "
                + "phone='" + c.getPhoneNumber()
                + "' WHERE csid='" + c.getStoreCardNumber() + "'";
        return updateDB(sqlText);
    }

     /**
     * This method retrieves all addresses associated with a customer.
     * @param csid an integer
     * @return array list of Address data transfer objects
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Address> getAddressList(int csid) {
        ArrayList<Address> list = new ArrayList<Address>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select a.aid, a.aptNo, a.stNo, a.street, a.postCode, "
                    + "a.city, a.province "
                    + "from customer c, address a, "
                    + "hasaddress h "
                    + "where c.csid=h.csid and a.aid=h.aid and c.csid=" + csid;

            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Address(rs.getInt(1),
                        rs.getString(2),
                        rs.getInt(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "CustomerDAO:getAddressList" + ex);
        }
        return list;
    }

    /**
     * This method inserts an address information into database
     * @param csid an integer for customer id
     * @param aid an integer for address id
     * @return a boolean flag
     * @throws {@link KwickKioskDBException}
     */
    public boolean addAddressToCustomer(int csid, int aid)
            throws KwickKioskDBException {
        String sqlText = "INSERT INTO hasaddress "
                + "(csid, aid) VALUES ("
                + csid + ", "
                + aid + ")";
        return updateDB(sqlText);
    }

    /**
     * This method removes an address information from database
     * @param csid an integer for customer id
     * @param aid an integer for address id
     * @return a boolean flag
     * @throws {@link KwickKioskDBException}
     */
    public boolean removeAddressToCustomer(int csid, int aid)
            throws KwickKioskDBException {
        String sqlText = "delete from hasaddress "
                + "where csid=" + csid + " and aid=" + aid;
        return updateDB(sqlText);
    }

    /**
     * This method updates database using data manipulation query
     * @param sqlText string for query
     * @return a boolean flag
     * @throws {@link KwickKioskDBException}
     */
    private boolean updateDB(String sqlText) throws KwickKioskDBException {
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            int value = sqlStatement.executeUpdate(sqlText);
            conn.commit();
            sqlStatement.close();
            if (value == 1) {
                return true;
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "CustomerDAO : updateDB" + ex);
            try {
                conn.rollback();
                throw new KwickKioskDBException("CustomerDAO : updateDB",
                    ex.getMessage());
            } catch (SQLException ex2) {
                throw new KwickKioskDBException("CustomerDAO : updateDB",
                    ex.getMessage());
            }
        }

        return false;
    }

    /**
     * This method retrieves clerk information from database
     * @param csid an integer
     * @return a customer data transfer object
     * @throws {@link KwickKioskDBException}
     * @throws EmptySet
     */
    public Customer searchByID(int csid) throws KwickKioskDBException, EmptySet {
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from customer c "
                    + "where c.csid=" + csid;
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            if (rs.next()) {
                Customer theCustomer = new Customer(rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getString(6));
                rs.close();
                sqlStatement.close();
                if (theCustomer == null) {
                    throw new EmptySet("CustomerDAO : searchByID",
                            "No result found");
                }
                return theCustomer;
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "CustomerDAO : searchByID" + ex);
            throw new KwickKioskDBException("CustomerDAO : searchByID",
                    ex.getMessage());
        }
        return null;
    }

    /**
     * This method checks if email provided by customer is already present in database
     * @param email a string
     * @return a boolean flag
     * @throws {@link KwickKioskDBException}
     */
    public boolean unqiueEmail(String email) throws KwickKioskDBException {
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from customer c "
                    + "where c.email='" + email + "'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            if (rs.next()) {
                sqlStatement.close();
                return true;
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "CustomerDAO : searchEmail" + ex);
            throw new KwickKioskDBException("CustomerDAO : uniqueEmail",
                    ex.getMessage());
        }
        return false;
    }

    /**
     * This method checks if phone provided by customer is already present in database
     * @param phone a string
     * @return a boolean
     * @throws {@link KwickKioskDBException}
     */
    public boolean unqiuePhone(String phone) throws KwickKioskDBException {
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from customer c "
                    + "where c.phone='" + phone + "'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            if (rs.next()) {
                sqlStatement.close();
                return true;
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null,
                    "CustomerDAO : uniquePhone" + ex);
            throw new KwickKioskDBException("CustomerDAO : uniquePhone",
                    ex.getMessage());
        }
        return false;
    }
}
